package ecom.appcode.dao.daointerface;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ecom.appcode.common.constants.CommonConstants;
import ecom.appcode.common.daobean.CategoryDaoBean;
import ecom.appcode.common.daobean.ShoesDaoBean;
import ecom.appcode.common.utility.DBUltils;

public class ShoesDaoImpl implements ShoesDao {

    /** Select top 8 Featured Items */
    public List<ShoesDaoBean> loadNewArrivalsItems(String date, int index) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT TOP 8 * FROM tblShoes t001 WHERE t001.dateCarry > ? ORDER BY shoesID DESC";
            stm = con.prepareStatement(sql);
            stm.setString(1, date);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                int categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);

                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Load a shoes */
    public ShoesDaoBean loadAShoesProduct(int shoesID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblShoes WHERE shoesID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, shoesID);
            rs = stm.executeQuery();
            ShoesDaoBean shoes = new ShoesDaoBean();
            if (rs.next()) {
                /** Shoes ID */
                shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                int categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage, categoryID, sex,
                        dateCarry, discount, description, brand);

            }
            return shoes;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Load shoes by category ID and Sex */
    public List<ShoesDaoBean> loadShoesbyCategoryIDAndSex(int categoryID, String sex) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblShoes WHERE categoryID = ? AND (sex = ? OR sex = ?)";
            stm = con.prepareStatement(sql);
            stm.setInt(1, categoryID);
            stm.setString(2, sex);
            stm.setString(3, CommonConstants.UNISEX);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                categoryID = rs.getInt("categoryID");
                /** shoes sex */
                sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);
                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Load Shoes by category ID */
    public List<ShoesDaoBean> loadShoesbyCategoryID(int categoryID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblShoes WHERE categoryID = ? AND dateCarry > ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, categoryID);
            stm.setString(2, CommonConstants.DATE_CARRY);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);
                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    public List<ShoesDaoBean> loadTop20SaleShoes() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT TOP 20 * FROM tblShoes WHERE discount < 100";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                int categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);

                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Search shoes by name and Color */
    public List<ShoesDaoBean> searchShoes(String name, String color) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = CommonConstants.BLANK;
            if (CommonConstants.BLANK.equals(name) || name == null) {
                sql = "SELECT * FROM tblShoes WHERE color = ?";
                stm = con.prepareStatement(sql);
                stm.setString(1, color);

            } else if (CommonConstants.VALUE_DEAFULT_COLOR.equals(color)
                    || color == CommonConstants.VALUE_DEAFULT_COLOR) {
                sql = "SELECT * FROM tblShoes WHERE name LIKE ?";
                stm = con.prepareStatement(sql);
                stm.setString(1, "%" + name + "%");

            } else {
                sql = "SELECT * FROM tblShoes WHERE name LIKE ? AND color = ? ";
                stm = con.prepareStatement(sql);
                stm.setString(1, "%" + name + "%");
                stm.setString(2, color);
            }
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String shoesColor = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                int categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, shoesColor, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);

                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Load color combo box */
    public List<ShoesDaoBean> loadColor() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT DISTINCT color FROM tblShoes";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes Color */
                String color = rs.getString("color");
                ShoesDaoBean shoesBean = new ShoesDaoBean(color);
                list.add(shoesBean);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }
    /** Load All Sale Items */
    public List<ShoesDaoBean> loadAllSaleItems() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblShoes WHERE discount < 100";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                int categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);

                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Select all Shoes for admin page */
    public List<ShoesDaoBean> loadAllShoes() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblShoes";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                int categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);

                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Add new shoes */
    public boolean addNewShoes(String name, String color, float unitPrice, int quantity, String urlImage,
            int categoryID, String sex, String dateCarry, float discount, String description, String brand) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "INSERT INTO tblShoes VALUES(?,?,?,?,?,?,?,?,?,?,?)";
            stm = con.prepareStatement(sql);

            stm.setString(1, name);
            stm.setString(2, color);
            stm.setFloat(3, unitPrice);
            stm.setInt(4, quantity);
            stm.setString(5, urlImage);
            stm.setInt(6, categoryID);
            stm.setString(7, sex);
            stm.setString(8, dateCarry);
            stm.setFloat(9, discount);
            stm.setString(10, description);
            stm.setString(11, brand);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;

    }

    /** Update Shoes */
    public boolean updateShoes(int shoesID, String name, String color, float unitPrice, int quantity, String urlImage,
            int categoryID, String sex, String dateCarry, float discount, String description, String brand) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "UPDATE tblShoes SET name = ?, color = ? ,unitPrice = ?, quantity = ?,image= ?,categoryID= ?,sex = ?,dateCarry = ?,discount =?, description = ?,brand = ? WHERE shoesID = ?";
            stm = con.prepareStatement(sql);

            stm.setString(1, name);
            stm.setString(2, color);
            stm.setFloat(3, unitPrice);
            stm.setInt(4, quantity);
            stm.setString(5, urlImage);
            stm.setInt(6, categoryID);
            stm.setString(7, sex);
            stm.setString(8, dateCarry);
            stm.setFloat(9, discount);
            stm.setString(10, description);
            stm.setString(11, brand);
            stm.setInt(12, shoesID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /** Delete Shoes */
    public boolean deleteShoes(int shoesID) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "DELETE FROM tblShoes WHERE shoesID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, shoesID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    /** Load Shoes New Arrivals */
    public List<ShoesDaoBean> loadShoesNewArrivals(int categoryID, String date) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblShoes WHERE categoryID = ? AND dateCarry > ? AND discount = 100";
            stm = con.prepareStatement(sql);
            stm.setInt(1, categoryID);
            stm.setString(2, date);
            rs = stm.executeQuery();
            List<ShoesDaoBean> list = new ArrayList<ShoesDaoBean>();
            while (rs.next()) {
                /** Shoes ID */
                int shoesID = rs.getInt("shoesID");
                /** Shoes Name */
                String shoesName = rs.getString("name");
                /** Shoes Color */
                String color = rs.getString("color");
                /** Shoes Price */
                float unitPrice = rs.getFloat("unitPrice");
                /** quantity in stock */
                int quantity = rs.getInt("quantity");
                /** url of Image */
                String urlImage = rs.getString("image");
                /** category ID */
                categoryID = rs.getInt("categoryID");
                /** shoes sex */
                String sex = rs.getString("sex");
                /** date carry */
                String dateCarry = rs.getString("dateCarry");
                /** discount % */
                float discount = rs.getFloat("discount");
                /** description */
                String description = rs.getString("description");
                /** Brand */
                String brand = rs.getString("brand");

                ShoesDaoBean shoes = new ShoesDaoBean(shoesID, shoesName, color, unitPrice, quantity, urlImage,
                        categoryID, sex, dateCarry, discount, description, brand);

                list.add(shoes);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }
}
